package com.situ.dao;

import com.situ.entity.User;
import com.situ.util.DBUtil;
import com.situ.vo.SexVO;

import java.util.ArrayList;
import java.util.List;

public class UserDao {

    public List<User> select(Integer page, Integer limit, String username, String tel) {

        //select * from user limit a,b   a:过去了几条了
        //a:开始数据的下标值　　0表示第一条
        //b:每页几条
        List<Object> params = new ArrayList<>();
        String sql = "select * from user ";
        sql += concat(username, tel, params);

        sql += " limit ?,?";
        params.add((page - 1) * limit);
        params.add(limit);
        return DBUtil.executeDQL(sql, User.class, params.toArray());
    }

    private String concat(String username, String tel, List<Object> params) {
        String result = "";
        if (username != null && !"".equals(username)) {
            result += " instr(username,?)>0 ";
            params.add(username);
        }
        if (tel != null && !"".equals(tel)) {
            if (!result.equals("")) {
                result += " and ";
            }
            result += " instr(tel,?)>0 ";
            params.add(tel);
        }
        if (!result.equals("")) {
            result = " where " + result;
        }
        return result;
    }

    //新增用户
    public int insert(User user) {
        return DBUtil.executeDML("insert into user(username,password,name,tel,sex,email,status) values(?,?,?,?,?,?,?)", user.getUsername(), user.getPassword(), user.getName(), user.getTel(), user.getSex(), user.getEmail(), user.getEmail());
    }

    public int delete(Integer userId) {
        return DBUtil.executeDML("delete from user where id = ?", userId);
    }

    public int update(User user) {
        return DBUtil.executeDML("update user set username=?,password=?,name=?,sex=?,tel=?,email=?,status=? where id=?", user.getUsername(), user.getPassword(), user.getName(), user.getSex(), user.getTel(), user.getEmail(), user.getStatus(), user.getId());
    }

    public List<User> info(Integer uid) {
        return DBUtil.executeDQL("select * from user where id = ?", User.class, uid);
    }

    public Long count(String username, String tel) {
        List<Object> params = new ArrayList<>();
        return DBUtil.executeCount("select count(*) from user " + concat(username, tel, params), params.toArray());
    }

    public List<User> selectByUsername(String username) {
        return DBUtil.executeDQL("select * from user where username = ?", User.class, username);
    }

    public List<SexVO> stat() {
        String sql = "select sex 'name',count(id) 'value' from user group by sex";
        return DBUtil.executeDQL(sql, SexVO.class);
    }
}
